/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Nhan
 */
public class Checker {
 public static User checkUser(String username, String password) {
        DBConnector db = new DBConnector();
        Connection con = db.getCon();

        String sql = "select * from Account where username='" + username
                + "' and password='" + password + "'" ;
        Statement stm;

        try {
            stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                String role = rs.getString(3);
                String firstName = rs.getString(4);
                String status = rs.getString(8);
                return new User(username, firstName, role, status);
            }
          stm.close();
          rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public static User getUserInfo(String username) {

        DBConnector db = new DBConnector();
        Connection con = db.getCon();

        String sql = "select * from Account where username ='" + username + "'";

        Statement stm;

        try {
            stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sql);
           while (rs.next()) {
                String firstName = rs.getString(4);
                String role = rs.getString(3);
                String status = rs.getString(8);
                return new User(username, firstName, role, status);
            }
          stm.close();
          rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public static ArrayList<Book> getSearchResult(String keyword, String filter) {
        
        DBConnector db = new DBConnector();
        Connection con = db.getCon();
        
        String sql = "select * from Book where " + filter +" like '%" + keyword + "%'";

        Statement stm;
        ArrayList<Book> searchResult = new  ArrayList<Book>();
        
        try {
            stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt(1);
                String title = rs.getString(2);
                String image = rs.getString(3);
                String author = rs.getString(4);
                String type = rs.getString(5);
                float price = rs.getFloat(6);
                searchResult.add(new Book(id, title, image, author, type, price));
            }
          stm.close();
          rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }
        return searchResult;
    }

    public static ArrayList<Book> getAllBook() {

        DBConnector db = new DBConnector();
        Connection con = db.getCon();

        String sql = "select * from Book";

        Statement stm;
        ArrayList<Book> searchResult = new  ArrayList<Book>();

        try {
            stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt(1);
                String title = rs.getString(2);
                String image = rs.getString(3);
                String author = rs.getString(4);
                String type = rs.getString(5);
                float price = rs.getFloat(6);
                searchResult.add(new Book(id, title, image, author, type, price));
            }
          stm.close();
          rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }
        return searchResult;
    }

    public static ArrayList<Book> getAllUser() {

        DBConnector db = new DBConnector();
        Connection con = db.getCon();

        String sql = "select * from Book";

        Statement stm;
        ArrayList<Book> searchResult = new  ArrayList<Book>();

        try {
            stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt(1);
                String title = rs.getString(2);
                String image = rs.getString(3);
                String author = rs.getString(4);
                String type = rs.getString(5);
                float price = rs.getFloat(6);
                searchResult.add(new Book(id, title, image, author, type, price));
            }
          stm.close();
          rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }
        return searchResult;
    }
    

    public static ArrayList<User> getAllAccount() {

        DBConnector db = new DBConnector();
        Connection con = db.getCon();

        String sql = "select * from Account";

        Statement stm;
        ArrayList<User> allAccount = new  ArrayList<User>();

        try {
            stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                String username = rs.getString(1);
                String firstName = rs.getString(3);
                String role = rs.getString(4);
                String status = rs.getString(8);
                
                allAccount.add(new User(username, firstName, role, status));
            }
          stm.close();
          rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }
        return allAccount;
    }

    public static ArrayList<Order> getAllOrders() {

        DBConnector db = new DBConnector();
        Connection con = db.getCon();

        String sql = "select o.orderID, o.dateTimePlaced, o.status, o.totalPayment ,o.username "
                    + "from Orders o, Account a, Book b, OrderBook ob "
                    + "where a.username = o.username and o.orderID = ob.orderID and ob.bookID = b.bookID";

        Statement stm;
        ArrayList<Order> allOrders = new  ArrayList<Order>();

        try {
            stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                //select o.orderID, o.dateTimePlaced, o.status, o.totalPayment ,o.username
                
                String orderID = rs.getString(1);
                String dateTimePlaced = rs.getString(2);
                String status = rs.getString(3);
                float totalPayment = rs.getFloat(4);
                String username = rs.getString(5);
                //Order(String orderID, String timePlaced, String status, float totlPayment, String username ) {
                allOrders.add(new Order(orderID, dateTimePlaced, status, totalPayment, username));
            }
          stm.close();
          rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }
        return allOrders;
    }
    
    
    public static Book getBookInfo(String id) {
        DBConnector db = new DBConnector();
        Connection con = db.getCon();
        String sql = "select * from Book where bookID =" + id;
        Statement stm;

        try {
            stm = con.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            while (rs.next()) {
                int bookID = Integer.valueOf(id);
                String title = rs.getString(2);
                String image = rs.getString(3);
                String author = rs.getString(4);
                String type = rs.getString(5);
                float price = rs.getFloat(6);
                return new Book(bookID, title, image, author, type, price);
            }
          stm.close();
          rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public static boolean changeUserStatus(String username, String status) {

        DBConnector db = new DBConnector();
        Connection con = db.getCon();

        String sql = "update Account set status='" + status +"' where username='"+ username +"'";

        try {

            Statement stm = con.createStatement();
            stm.executeUpdate(sql);

        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        return true;
    }
    
    public static boolean insertOrderToDatabase(String username, float totalPayment ) {
        DBConnector db = new DBConnector();
        Connection con = db.getCon();
        
        DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
        String date = dateFormat.format(new Date());

        String sql = "insert into Orders(dateTimePlaced,username,status,totalPayment)"
                    + " values ('"+ date +"','"+ username +"','PENDING',"+ totalPayment  +")";


        try {
            Statement stm = con.createStatement();

            stm.executeUpdate(sql);
            
        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
        return true;
    }
}
